package com.dlc.shop.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONValidator;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.dlc.shop.bean.dto.SkuAdminDTO;
import com.dlc.shop.bean.dto.StockPointSkuDTO;
import com.dlc.shop.bean.enums.*;
import com.dlc.shop.bean.event.GetTransportNamesEvent;
import com.dlc.shop.bean.model.*;
import com.dlc.shop.bean.param.*;
import com.dlc.shop.bean.vo.WarehouseVO;
import com.dlc.shop.common.bean.LangConfig;
import com.dlc.shop.common.bean.LangItemConfig;
import com.dlc.shop.common.bean.SysProdConfig;
import com.dlc.shop.common.bean.UploadFile;
import com.dlc.shop.common.config.Constant;
import com.dlc.shop.common.constants.SegmentIdKey;
import com.dlc.shop.common.constants.StockPointType;
import com.dlc.shop.common.enums.SysTypeEnum;
import com.dlc.shop.common.exception.YamiShopBindException;
import com.dlc.shop.common.i18n.I18nMessage;
import com.dlc.shop.common.util.Json;
import com.dlc.shop.common.util.PageParam;
import com.dlc.shop.common.util.PoiExcelUtil;
import com.dlc.shop.config.ShopConfig;
import com.dlc.shop.dao.ProductMapper;
import com.dlc.shop.manager.impl.LangManager;
import com.dlc.shop.service.*;
import jakarta.servlet.http.HttpServletResponse;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.jetbrains.annotations.NotNull;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author yami
 */
@Service
@AllArgsConstructor
@Slf4j
public class ProductExcelServiceImpl implements ProductExcelService {

    private final ProductMapper productMapper;
    private final ProductService productService;
    private final CategoryService categoryService;
    private final CategoryLangService categoryLangService;
    private final ApplicationContext applicationContext;
    private final SkuService skuService;
    private final SegmentService segmentService;
    private final CategoryShopService categoryShopService;
    private final SysConfigService sysConfigService;
    private final LangManager langManager;
    private final WarehouseService warehouseService;
    private static final String SEMICOLON = ";";
    private static final String COLON = ":";

    private static final String COMMA = ",";

    private static final String LEFT_BRACKET = "（";
    private static final Integer VALID_NUM = 2;

    /**
     * 当天有效
     */
    private static final String TODAY = "当天有效";

    /**
     * 长期有效
     */
    private static final String LONG_TERM = "长期有效";

    private final ShopConfig shopConfig;

    private final static Pattern IMG_URL = Pattern
            .compile(".*?(image/gif|image/jpeg|image/png|image/jpg|image/bmp)");

    private final static Pattern IMG_URL_SUFFIX = Pattern
            .compile(".*?(gif|jpeg|png|jpg|bmp)");


    /**
     * 上传视大小频限制
     */
    private final int MAX_LENGTH_BYMGB = 50;

    /**
     * 定义MB的计算常量
     */
    private final int MB = 1024 * 1024;

    /**
     * 虚拟商品留言条数限制
     */
    private final int VIRTUAL_MSG_NUM = 10;

    /**
     * 图片数量限制
     */
    private final int IMG_NUM = 9;



    @Override
    public int writerProdToExcel(List<ProductExportParam> products, ExcelWriter writer, Long shopId, Integer row, Map<Long, String> categoryMap, LangConfig langConfig) {
        if (CollUtil.isEmpty(products)) {
            return row;
        }
        Integer lang = I18nMessage.getLang();
        List<Long> prodIds = products.stream().map(ProductExportParam::getProdId).collect(Collectors.toList());
        List<ProductExportParam> productExportParams = productMapper.listProdsByProdParam(prodIds);
        if (CollUtil.isEmpty(productExportParams)) {
            return row;
        }
        // 获取店铺分类数据
        Set<Long> shopCategoryIds = productExportParams.stream().map(ProductExportParam::getShopCategoryId).filter(shopCategoryId -> Objects.nonNull(shopCategoryId)).collect(Collectors.toSet());
        List<Category> shopCategories = categoryService.listByCategoryIds(shopCategoryIds);
        Map<Long, String> shopCategoryMap = shopCategories.stream().filter(category -> Objects.nonNull(category.getCategoryId())).collect(Collectors.toMap(Category::getCategoryId, Category::getCategoryName));


        List<Sku> skuData = skuService.listSkuAndLangByProdIds(prodIds);
        Map<Long, List<Sku>> skuMap = skuData.stream().filter(sku -> Objects.nonNull(sku.getSkuId())).collect(Collectors.groupingBy(Sku::getProdId));
        // 商品国际化处理
        Map<Long, ProductExportParam> prodMap = productExportParams.stream().collect(Collectors.toMap(ProductExportParam::getProdId, p -> p));
        for (ProductExportParam esProd : products) {
            ProductExportParam prod = prodMap.get(esProd.getProdId());
            List<Sku> skuList = skuMap.get(esProd.getProdId());
            if (Objects.isNull(prod) || CollUtil.isEmpty(skuList)) {
                continue;
            }
            int firstRow = row + 1;
            int lastRow = row + skuList.size();
            int col = -1;
            col = writerProdData(writer, shopId, prod, esProd, firstRow, lastRow, col, categoryMap, shopCategoryMap,langConfig, lang);
            // 商品参数
            for (Sku sku : skuList) {
                int index = col;
                row++;
                Map<Integer, SkuLang> skuLangMap;
                if (CollUtil.isEmpty(sku.getSkuLangList())) {
                    skuLangMap = new HashMap<>(16);
                    System.out.println();
                } else {
                    skuLangMap = sku.getSkuLangList().stream().filter(skuLang -> Objects.nonNull(skuLang.getLang())).collect(Collectors.toMap(SkuLang::getLang, p -> p));
                }

                if (MapUtil.isNotEmpty(skuLangMap) && !skuLangMap.containsKey(langConfig.getLang())) {
                    SkuLang newSkuLang = new SkuLang();
                    newSkuLang.setLang(langConfig.getLang());
                    SkuLang skuLang = skuLangMap.get(langManager.getLang(skuLangMap.keySet(), langConfig, lang));
                    newSkuLang.setProdName(skuLang.getProdName());
                    newSkuLang.setProperties(skuLang.getProperties());
                }
                // 商品名称
                for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
                    SkuLang skuLang = skuLangMap.get(langItemConfig.getLang());
                    if (Objects.nonNull(skuLang)) {
                        writer.writeCellValue(++index, row, skuLang.getSkuName());
                    } else {
                        writer.writeCellValue(++index, row, "");
                    }
                }
                // 属性名称
                for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
                    SkuLang skuLang = skuLangMap.get(langItemConfig.getLang());
                    if (Objects.nonNull(skuLang)) {
                        writer.writeCellValue(++index, row, skuLang.getProperties());
                    } else {
                        writer.writeCellValue(++index, row, "");
                    }
                }
                // 原价
                writer.writeCellValue(++index, row, sku.getOriPrice());
                // 实际价格
                writer.writeCellValue(++index, row, sku.getPrice());
                // 库存
                writer.writeCellValue(++index, row, sku.getStocks());
                // 商家编码
                writer.writeCellValue(++index, row, sku.getPartyCode());
                // 商品重量
                writer.writeCellValue(++index, row, sku.getWeight());
                // 商品体积
                writer.writeCellValue(++index, row, sku.getVolume());
                //库存预警
                writer.writeCellValue(++index,row,sku.getStockWarning());
            }
        }
        return row;
    }

    @Override
    public ExcelWriter getProdExcelWriter(Long shopId, LangConfig langConfig, boolean template) {
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 商品导出or模板
        List<String> headerList = getHeaderList(shopId, langConfig, template);
        getSheet(writer, shopId, langConfig.getLangItemList().size());
        writer.merge(headerList.size() - 1, "商品信息整理");
        writer.writeRow(headerList);
        return writer;
    }

    @Override
    public void downloadModel(HttpServletResponse response, Long shopId) {
        LangConfig langConfig = langManager.getLangConfig();
        //通过工具类创建writer
        ExcelWriter writer = getProdExcelWriter(shopId, langConfig, true);
// TODO -m
        try (Workbook workbook = writer.getWorkbook()) {
            Sheet sheet = writer.getSheet();
            setComment(sheet.getRow(1).getCell(langConfig.getLangItemList().size() + 4),"1、选填，请填写图片在图片服务器的URL地址;\n" +
                    "2、最多可上传9张，不同图片请以“,”隔开" ,sheet);

            setComment(sheet.getRow(1).getCell(langConfig.getLangItemList().size() + 5),"1、选填，请填写视频在视频服务器的URL地址;\n" +
                    "2、只能添加一个视频链接;\n" +
                    "3、 视频仅支持mp4格式,最大限制50M;",sheet);
            setComment(sheet.getRow(1).getCell(langConfig.getLangItemList().size() * 3 + 6), "默认支持快递配送", sheet);
            dropDownList(shopId, sheet, workbook, langConfig);
            PoiExcelUtil.writeExcel(response, writer);
        } catch (Exception e) {
            log.error("Exception:", e);
        }
    }

    @Override
    public Object parseFile(MultipartFile file, Long shopId, Long employeeId, List<Long> prodIds, boolean isSameCity) throws Exception {
        Integer lang = I18nMessage.getDbLang();
        if (!PoiExcelUtil.checkFileMagic(file.getInputStream())) {
            return I18nMessage.getMessage("yami.file.error");
        }
        try (InputStream inputStream = file.getInputStream();
            Workbook workBook = WorkbookFactory.create(inputStream)) {
            //读取工作表
            Sheet sheet = workBook.getSheetAt(0);
            int rowNumber = sheet.getPhysicalNumberOfRows();
            //校验是否填写内容
            if (rowNumber <= Constant.EXCEL_BEGIN_ROW) {
                // 文件无内容
                throw new YamiShopBindException("yami.document.no.content");
            }

            // 获取运费模板map
            List<TransportParam> transportParams = new ArrayList<>();
            applicationContext.publishEvent(new GetTransportNamesEvent(transportParams, shopId));
            Map<String, Long> transportMap = transportParams.stream().collect(Collectors.toMap(TransportParam::getTransName, TransportParam::getTransportId, (key1, key2) -> key2));
            transportMap.put(DeliveryTemplateType.FREE_SHIPPING.getName(), DeliveryTemplateType.FREE_SHIPPING.getValue());
            transportMap.put(DeliveryTemplateType.FREIGHT.getName(), DeliveryTemplateType.FREIGHT.getValue());

            //获取已有的，未被删除的商品编码
            List<Product> dbProducts = productMapper.selectList(new LambdaQueryWrapper<Product>().eq(Product::getShopId, shopId)
                    .ne(Product::getStatus, -1));
            List<Long> dbProdIds = dbProducts.stream().map(Product::getProdId).collect(Collectors.toList());
            List<String> partyCodes = skuService.listSkuByProdIds(dbProdIds, null);
            HashSet<String> partyCodeSet = new HashSet<>();
            // 获取商品审核开关
            SysProdConfig sysProdConfig = sysConfigService.getSysConfigObject(Constant.PROD_SWITCH_CONFIG, SysProdConfig.class);
            Boolean prodAudit = sysProdConfig.getProdAudit();
            List<ProductParam> products = new ArrayList<>();
            List<String> errorList = new ArrayList<>();
            Set<Integer> errorSet = new HashSet<>();
            List<SkuAdminDTO> skuList = new ArrayList<>();
            int insertRows = loadProdData(shopId, isSameCity, sheet, rowNumber, transportMap, partyCodes, partyCodeSet, prodAudit, products, errorList, errorSet, skuList);
//            int totalRows = (rowNumber - Constant.EXCEL_BEGIN_ROW);
            int totalRows = products.size();
            String response;
            //过滤删除状态的商品
            products = products.stream().filter(prod -> !prod.getStatus().equals(ProdStatusEnums.DELETE.getValue())).collect(Collectors.toList());
            //不为空批量导
            if (CollUtil.isNotEmpty(products)) {
                int errorCount = errorList.size();
                saveImportProd(prodIds, products, errorList, errorSet, employeeId);
                errorCount = errorList.size() - errorCount;
                response = "检查到" + totalRows + "条商品，成功导入" +
                        (products.size() - errorCount) + "条商品信息！错误" + errorSet.size() + "条商品信息！" +
                        (CollUtil.isNotEmpty(errorList) ? errorList.toString() : "");
                return response;
            }
            response = I18nMessage.getMessage("yami.common.excel.import.zero.error");
            if (CollUtil.isNotEmpty(errorList)) {
                response = response + errorList.toString();
            }
            return response;
        } catch (Exception e) {
            return e.getMessage();
        }
    }

    @Override
    public void prodEffectExport(HttpServletResponse response, ProdEffectParam param, Integer lang) {

        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 导出模板
        String[] header = {"店铺名称", "商品名称", "商品价格", "商品状态", "曝光次数", "曝光人数", "加购人数", "加购件数", "下单人数", "支付人数", "下单件数", "支付件数",
                "下单金额", "支付金额", "单品转化率", "申请退款订单数", "申请退款人数", "成功退款订单数", "成功退款人数", "成功退款金额", "退款率"};
        List<String> headerList = Arrays.asList(header);
        Sheet sheet = writer.getSheet();
        sheet.setColumnWidth(0, 40 * 256);
        sheet.setColumnWidth(1, 40 * 256);
        sheet.setColumnWidth(2, 40 * 256);
        for (int i = 3; i <= headerList.size(); i++) {
            sheet.setColumnWidth(i, 30 * 256);
        }

        writer.merge(headerList.size() - 1, "商品洞察");
        writer.writeRow(headerList);
        if (Objects.equals(1, param.getGroup())) {
            return;
        }
        IPage<ProdEffectRespParam> pageProdEffect = productService.pageProdEffect(new PageParam<>(), param, I18nMessage.getDbLang(), true);
        if (CollUtil.isEmpty(pageProdEffect.getRecords())) {
            PoiExcelUtil.writeExcel(response, writer);
            return;
        }
        try {
            int row = 1;
            // 写入商品洞察信息
            for (ProdEffectRespParam prod : pageProdEffect.getRecords()) {
                int col = -1;
                PoiExcelUtil.mergeIfNeed(writer, ++row, row, ++col, col, prod.getShopName());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getProdName());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPrice());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getProdStatus());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getExpose());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getExposePersonNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getAddCartPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getAddCart());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getSingleProdRate());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessRate());
            }
            PoiExcelUtil.writeExcel(response, writer);
        } catch (Exception e) {
            log.error("商品洞察导出:", e);
            throw new YamiShopBindException(e.getMessage());
        }
    }

    @Override
    public void dropDownList(Long shopId, Sheet sheet, Workbook workbook, LangConfig langConfig) {
        int size = langConfig.getLangItemList().size();
        int prodTypeIndex =  size;
        int moldIndex = 1 + size;
        // 平台分类的索引
        int platformCategoryIndex = 2 + size;
        // 店铺分类的索引
        int shopCategoryIndex = 3 + size;
        int dvyTypeIndex = 6 + size * 3;
        int transportIndex = 7 + size * 3;
        int statusIndex = 9 + size * 3;
        int writeOffTypeIndex = 10 + size * 3;
        int isRefundIndex = 15 + size * 3;

        // shopId: null-导出商城的商品,比店铺模板多了一个店铺名称，所以对应属性的索引要+1
        if (Objects.isNull(shopId)) {
            prodTypeIndex++;
            moldIndex++;
            dvyTypeIndex++;
            transportIndex++;
            statusIndex++;
            writeOffTypeIndex++;
            isRefundIndex++;
        }


        if (Objects.isNull(shopId)) {
            shopId = Constant.PLATFORM_SHOP_ID;
        }
        // 限制类型
        String[] prodType = {"普通商品", "组合选品"};
        PoiExcelUtil.createDropDownList(sheet, prodType, 2, 50000, prodTypeIndex, prodTypeIndex);
        // 限制类别
        String[] prodCategory = {"实物商品", "虚拟商品"};
        PoiExcelUtil.createDropDownList(sheet, prodCategory, 2, 50000, moldIndex, moldIndex);

        categoryDropDownList(shopId, sheet, workbook, platformCategoryIndex, shopCategoryIndex);

        // 限制物流方式
        String[] dvyTypes = {"用户自提", "同城配送", "用户自提且同城配送"};
        PoiExcelUtil.createDropDownList(sheet, dvyTypes, 2, 50000, dvyTypeIndex, dvyTypeIndex);

        // 组装运费模板
        List<TransportParam> transportParams = new ArrayList<>();
        applicationContext.publishEvent(new GetTransportNamesEvent(transportParams, shopId));
        List<String> transportNameList = new ArrayList<>();
        transportNameList.add(DeliveryTemplateType.FREE_SHIPPING.getName());
        transportNameList.add(DeliveryTemplateType.FREIGHT.getName());
        if (CollUtil.isNotEmpty(transportParams)) {
            transportParams.forEach(transportParam -> transportNameList.add(transportParam.getTransName()));
        }
        PoiExcelUtil.createDropDownList(workbook, sheet, transportNameList, 2, 50000, transportIndex, transportIndex);

        String[] statusStr = {"下架", "上架"};
        PoiExcelUtil.createDropDownList(sheet, statusStr, 2, 50000, statusIndex, statusIndex);

        // 限制核销次数类型 -1.多次核销 0.无需核销 1.单次核销
        String[] writeOffType = {"多次核销", "无需核销", "单次核销"};
        PoiExcelUtil.createDropDownList(sheet, writeOffType, 2, 50000, writeOffTypeIndex, writeOffTypeIndex);

        // 核销有效期 -1.长期有效 0.自定义 x.x天内有效
//        String[] writeOffTime = {"长期有效","自定义","x天内有效"};
//        createDropDownList(sheet,writeOffTime,2,50000,11,11);
        // 是否可以退款 1.可以 0.不可以
        String[] isRefund = {"是", "否"};
        PoiExcelUtil.createDropDownList(sheet, isRefund, 2, 50000, isRefundIndex, isRefundIndex);
    }

    private void categoryDropDownList(Long shopId, Sheet sheet, Workbook workbook, int platformCategoryIndex, int shopCategoryIndex) {
        if (Objects.equals(shopId, Constant.PLATFORM_SHOP_ID)) {
            return;
        }
        // 限制商家分类和平台分类
        List<Category> platformCategoryList = this.listPlatformCategory(shopId);

        if (CollUtil.isNotEmpty(platformCategoryList)) {
            String[] categoryName = new String[platformCategoryList.size()];
            for (int i = 0; i < platformCategoryList.size(); i++) {
                categoryName[i] = platformCategoryList.get(i).getCategoryName();
            }
            PoiExcelUtil.createDropDownList(workbook, sheet, Arrays.asList(categoryName), 2, 50000, platformCategoryIndex, platformCategoryIndex);
        }
        List<Category> shopCategories = categoryService.list(new LambdaQueryWrapper<Category>().eq(Category::getShopId, shopId).eq(Category::getStatus, ProdStatusEnums.NORMAL.getValue()));
        if (CollUtil.isNotEmpty(shopCategories)) {
            String[] categoryName = new String[shopCategories.size()];
            langManager.getCategoryLang(shopCategories);
            for (int i = 0; i < shopCategories.size(); i++) {
                categoryName[i] = shopCategories.get(i).getCategoryName();
            }
            PoiExcelUtil.createDropDownList(workbook, sheet, Arrays.asList(categoryName), 2, 50000, shopCategoryIndex, shopCategoryIndex);
        }
    }

    @Override
    public List<Category> listPlatformCategory(Long shopId) {
        List<Category> categories = categoryShopService.listSigningCategoryAndParent(shopId);
        categories.sort(Comparator.comparing(Category::getParentId));
        List<Category> categoryList = new ArrayList<>();
        if (Objects.isNull(categories)) {
            return categoryList;
        }
        Map<Long, Category> categoryMap = categories.stream()
                .filter(category -> !Objects.equals(category.getGrade(), CategoryLevel.THIRD.value()))
                .collect(Collectors.toMap(Category::getCategoryId, c -> c));
        for (Category category : categories) {
            // 一二级分类不用处理
            if(!Objects.equals(category.getGrade(), CategoryLevel.THIRD.value())) {
                continue;
            }
            Category secondCategory = categoryMap.get(category.getParentId());
            if (Objects.isNull(secondCategory)) {
                continue;
            }
            Category firstCategory = categoryMap.get(secondCategory.getParentId());
            if (Objects.isNull(firstCategory)) {
                continue;
            }
            String categoryName = firstCategory.getCategoryName() + "/" + secondCategory.getCategoryName() + "/" +category.getCategoryName();
            category.setCategoryName(categoryName);
            categoryList.add(category);
        }
        return categoryList;
    }

    public void saveImportProd(List<Long> prodIds, List<ProductParam> products, List<String> errorList, Set<Integer> errorSet, Long employeeId) {
        int count = 0;
        for (ProductParam productParam : products) {
            count++;
            try {
                // 有事务，发生错误会回滚
                productParam.setEmployeeId(employeeId);
                productService.saveProduct(productParam);
                prodIds.add(productParam.getProdId());
            } catch (Exception e) {
                //错误信息提示
                errorList.add("第" + count + "行数据有错误:" + e.getMessage());
                errorSet.add(count);
            }
        }
    }

    private int loadProdData(Long shopId, boolean isSameCity, Sheet sheet, int rowNumber, Map<String, Long> transportMap, List<String> partyCodes, HashSet<String> partyCodeSet, Boolean prodAudit, List<ProductParam> products, List<String> errorList, Set<Integer> errorSet, List<SkuAdminDTO> skuList) {
        // 获取平台分类map
        List<Category> platformCategoryList = this.listPlatformCategory(shopId);
        if (CollUtil.isEmpty(platformCategoryList)) {
            // 当前店铺没有签约的平台分类，请先签约分类
            throw new YamiShopBindException("yami.shop.not.category");
        }

        // 如果重复的key取最后一个的值
        Map<String, Long> categoryMap = platformCategoryList.stream().collect(Collectors.toMap(Category::getCategoryName, Category::getCategoryId, (key1, key2) -> key2));
        // 获取店铺分类map
        List<Category> shopCategories = categoryService.list(new LambdaQueryWrapper<Category>().eq(Category::getShopId, shopId).eq(Category::getStatus, ProdStatusEnums.NORMAL.getValue()));
        Map<String, Long> shopCategoryMap = getCategoryNameMap(shopCategories);

        LangConfig langConfig = langManager.getLangConfig();
        Integer[] langArray = getProdExcelLang(sheet);
        Date date = new Date();
        Integer totalStocks = 0;
        double price = 0.0;
        double oriPrice = 0.0;
        int insertRows = 0;
        ProductParam product = new ProductParam();
        //循环读取每一行数据并校验
        for (int i = 2; i < rowNumber; i++) {
            boolean mergedRegion = PoiExcelUtil.isMergedRegion(sheet, i, 0);
            try {
                //读取行
                Row row = sheet.getRow(i);
                Integer cell = -1;
                Map<Integer, ProdLangParam> prodLangMap = new HashMap<>(16);
                for (Integer lang : langArray) {
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String prodName = PoiExcelUtil.getRowValue(row, ++cell, false);
                    if (StrUtil.isBlank(prodName)) {
                        continue;
                    }
                    ProdLangParam prodLang = prodLangMap.get(lang);
                    if (Objects.isNull(prodLang) && StrUtil.isNotBlank(prodName)) {
                        prodLang = new ProdLangParam();
                        prodLangMap.put(lang, prodLang);
                    }
                    prodLang.setLang(lang);
                    prodLang.setProdName(prodName);
                }

                String prodType = PoiExcelUtil.getRowValue(row, ++cell, false);
                String prodCategory = PoiExcelUtil.getRowValue(row, ++cell, false);
                String category = PoiExcelUtil.getRowValue(row, ++cell, false);
                String shopCategory = PoiExcelUtil.getRowValue(row, ++cell, false);
                String pic = PoiExcelUtil.getRowValue(row, ++cell, false);
                String video = PoiExcelUtil.getRowValue(row, ++cell, false);
                // 卖点
                for (Integer lang : langArray) {
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String brief = PoiExcelUtil.getRowValue(row, ++cell, false);
                    ProdLangParam prodLang = prodLangMap.get(lang);
                    if (Objects.isNull(prodLang)) {
                        continue;
                    }
                    prodLang.setBrief(brief);
                }
                // 商品详情
                for (Integer lang : langArray) {
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String content = PoiExcelUtil.getRowValue(row, ++cell, false);
                    ProdLangParam prodLang = prodLangMap.get(lang);
                    if (Objects.isNull(prodLang)) {
                        continue;
                    }
                    prodLang.setContent(content);
                }
                String dvyType = PoiExcelUtil.getRowValue(row, ++cell, false);
                String transportName = PoiExcelUtil.getRowValue(row, ++cell, false);
                String freightAmount = PoiExcelUtil.getRowValue(row, ++cell, true);
                String statusStr = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffNum = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffMultipleCount = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffTime = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffStart = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffend = PoiExcelUtil.getRowValue(row, ++cell, false);
                String isRefund = PoiExcelUtil.getRowValue(row, ++cell, false);
                String virtualRemark = PoiExcelUtil.getRowValue(row, ++cell, false);

                Map<Integer, String> prodParameterLangMap = new HashMap<>(16);
                for (Integer lang : langArray) {
                    if (lang == -1) {
                        cell++;
                        continue;
                    }
                    String prodParameterStr = PoiExcelUtil.getRowValue(row, ++cell, false);
                    prodParameterLangMap.put(lang, prodParameterStr);
                }
                // product赋值
                boolean isInitProd = prodLangMap.containsKey(langConfig.getLang())
                        || !mergedRegion
                        || i == sheet.getMergedRegion(i).getFirstRow();
                if (isInitProd) {
                    skuList = new ArrayList<>();
                    product = new ProductParam();
                    this.newProdInfo(shopId, isSameCity, categoryMap, shopCategoryMap, transportMap, prodAudit, products, product, prodLangMap, prodType, prodCategory, category, shopCategory,pic,video, dvyType, transportName,freightAmount, statusStr, writeOffNum, writeOffMultipleCount, writeOffTime, writeOffStart, writeOffend, isRefund, virtualRemark, prodParameterLangMap,langConfig);
                    totalStocks = 0;
                    oriPrice = 0.0;
                    price = 0.0;
                    insertRows++;
                }
                Map<Integer, SkuLang> skuLangMap = new HashMap<>(16);
                prodLangMap = product.getProdLangList().stream().collect(Collectors.toMap(ProdLangParam::getLang, p->p));
                SkuAdminDTO sku = getSkuAndsetSkuParam(shopId, partyCodes, partyCodeSet, langConfig, langArray, date, row, cell, prodLangMap, mergedRegion, skuLangMap);
                oriPrice = Objects.equals(oriPrice, 0.0) ? sku.getOriPrice() : oriPrice;
                price = Objects.equals(price, 0.0) ? sku.getPrice() : price;
                sku.setSkuLangList(new ArrayList<>(skuLangMap.values()));
                skuList.add(sku);
                // 如果prodName为空并且为合并行则为一个prod
                totalStocks += sku.getStocks();
                if (price > sku.getPrice()) {
                    oriPrice = sku.getOriPrice();
                    price = sku.getPrice();
                }
                product.setOriPrice(oriPrice);
                product.setScorePrice(0);
                product.setPrice(price);
                product.setTotalStocks(totalStocks);
                product.setSkuList(skuList);
            } catch (NumberFormatException numberFormatException) {
                errorList.add("第" + (i + 1) + "行数据有错误：" + "输入内容[" + numberFormatException.getMessage().substring(18) + "]格式错误");
                errorSet.add(i + 1);
                product.setStatus(ProdStatusEnums.DELETE.getValue());
                // 若是合并单元格数据报错，则跳到合并单元格的下一行
                if (mergedRegion) {
                    i = sheet.getMergedRegion(i).getLastRow();
                }
            } catch (Exception e) {
                //错误信息提示
                errorList.add("第" + (i + 1) + "行数据有错误：" + e.getMessage());
                errorSet.add(i + 1);
                product.setStatus(ProdStatusEnums.DELETE.getValue());
                // 若是合并单元格数据报错，则跳到合并单元格的下一行
                if (mergedRegion) {
                    i = sheet.getMergedRegion(i).getLastRow();
                }
            }
        }
        List<ProductParam> wrongList = products.stream().filter(prod -> CollUtil.isNotEmpty(prod.getSkuList()) && prod.getSkuList().size() > 100).collect(Collectors.toList());
        if (CollUtil.isNotEmpty(wrongList)) {
            // 检测到存在超过100组规格的商品，请修改后重新导入
            throw new YamiShopBindException("yami.exprot.prod.sku.error");
        }
        return insertRows;
    }

    @NotNull
    private SkuAdminDTO getSkuAndsetSkuParam(Long shopId, List<String> partyCodes, HashSet<String> partyCodeSet, LangConfig langConfig, Integer[] langArray, Date date, Row row, Integer cell, Map<Integer, ProdLangParam> prodLangMap, boolean mergedRegion, Map<Integer, SkuLang> skuLangMap) {
        // sku名称
        for (Integer lang : langArray) {
            if (lang == -1) {
                cell++;
                continue;
            }
            String skuName = PoiExcelUtil.getRowValue(row, ++cell, false);
            if (!Objects.equals(lang, langConfig.getLang()) && StrUtil.isBlank(skuName)) {
                continue;
            }
            SkuLang skuLang = skuLangMap.get(lang);
            if (Objects.isNull(skuLang)) {
                skuLang = new SkuLang();
                ProdLangParam prodLang = prodLangMap.get(lang);
                skuLang.setProdName(prodLang.getProdName());
                skuLangMap.put(lang, skuLang);
            }
            if (mergedRegion && Objects.equals(lang, langConfig.getLang()) && StrUtil.isBlank(skuName)) {
                throw new YamiShopBindException("(" + I18nMessage.getMessage("yami.excel.prod.sku.name") + "-" + langConfig.getName() + ") " + I18nMessage.getMessage("yami.product.sku.name.not.null"));
            }
            skuLang.setLang(lang);
            skuLang.setSkuName(skuName);
        }
        // sku销售规格
        for (Integer lang : langArray) {
            if (lang == -1) {
                cell++;
                continue;
            }
            String properties = PoiExcelUtil.getRowValue(row, ++cell, false);
            SkuLang skuLang = skuLangMap.get(lang);
            if (Objects.isNull(skuLang)) {
                continue;
            }
            skuLang.setProperties(properties);
        }
        // sku原价
        double skuOriPrice = Double.parseDouble(PoiExcelUtil.getRowValue(row, ++cell, true));
        if (Objects.isNull(skuOriPrice) || skuOriPrice < Constant.MIN_PRODUCT_AMOUNT) {
            skuOriPrice = Constant.MIN_PRODUCT_AMOUNT;
        }
        // sku价格
        double skuPrice = Double.parseDouble(PoiExcelUtil.getRowValue(row, ++cell, true));
        if (Objects.isNull(skuPrice) || skuPrice < Constant.MIN_PRODUCT_AMOUNT) {
            skuPrice = Constant.MIN_PRODUCT_AMOUNT;
        }
        // 库存
        Integer stocks = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true)).intValue();
        // 商品编码
        String partyCode = PoiExcelUtil.getRowValue(row, ++cell, false);
        if (StrUtil.isBlank(partyCode) || partyCodes.contains(partyCode) || partyCodeSet.contains(partyCode)) {
            partyCode = StringUtils.join("RM", String.valueOf(segmentService.getSegmentId(SegmentIdKey.PRODUCT)));
        }
        partyCodeSet.add(partyCode);
        // 商品重量
        double weight = Double.parseDouble(PoiExcelUtil.getRowValue(row, ++cell, true));
        // 商品体积
        double volume = Double.parseDouble(PoiExcelUtil.getRowValue(row, ++cell, true));
        // 库存预警
        int stockWarning = Integer.parseInt(PoiExcelUtil.getRowValue(row, ++cell, true));
        if(Objects.nonNull(stockWarning)&&stockWarning<0){
            throw new YamiShopBindException("yami.product.excel.import.stockWarnError");
        }
        SkuAdminDTO sku = this.getSku(shopId, date, skuOriPrice, skuPrice, stocks, partyCode, weight, volume,stockWarning);
        return sku;
    }

    @NotNull
    private Map<String, Long> getCategoryNameMap(List<Category> categories) {
        List<Long> categoryIds = categories.stream().map(Category::getCategoryId).collect(Collectors.toList());
        List<CategoryLang> list = categoryLangService.list(new LambdaQueryWrapper<CategoryLang>().in(CategoryLang::getCategoryId, categoryIds));
        // 如果重复的key取最后一个的值
        Map<String, Long> categoryMap = list.stream().collect(Collectors.toMap(CategoryLang::getCategoryName, CategoryLang::getCategoryId, (key1, key2) -> key2));
        return categoryMap;
    }

    private Integer[] getProdExcelLang(Sheet sheet) {
        LangConfig langConfig = langManager.getLangConfig();
        Map<String, Integer> langMap = langConfig.getLangItemList().stream().collect(Collectors.toMap(LangItemConfig::getName, LangItemConfig::getLang));
        List<Integer> list = new ArrayList<>();
        Row row = sheet.getRow(1);
        int cell = -1;
        boolean hasNext = false;
        boolean defaultLang = false;
        do {
            hasNext = false;
            String prodName = PoiExcelUtil.getRowValue(row, ++cell, false);
            if (StrUtil.isBlank(prodName)) {
                list.add(-1);
                continue;
            }
            String[] split = prodName.split("-");
            if (ArrayUtil.isEmpty(split) || split.length < VALID_NUM) {
                break;
            }
            String language = split[1];
            if (StrUtil.contains(language, LEFT_BRACKET)) {
                String[] tagArray = language.split(LEFT_BRACKET);
                if (ArrayUtil.isNotEmpty(tagArray) || tagArray.length == VALID_NUM) {
                    language = tagArray[0];
                }
            }
            Integer lang = langMap.get(language);
            if (Objects.isNull(lang)) {
                list.add(-1);
                continue;
            }
            if (Objects.equals(lang, langConfig.getLang())) {
                defaultLang = true;
            }
            list.add(lang);
            hasNext = true;
        } while (hasNext);
        if (!defaultLang) {
            throw new YamiShopBindException("yami.product.excel.import.notDefaultLang");
        }
        if (CollUtil.isEmpty(list)) {
            throw new YamiShopBindException("yami.product.excel.import.templateError");
        }
        return list.toArray(new Integer[0]);
    }

    public String checkProperties(String properties, ProductParam product) {
        if (StrUtil.isBlank(properties)) {
            return properties;
        }
        StringBuilder stringBuilder = new StringBuilder();
        if (!hasSemicolon(properties)) {
            if (properties.split(COLON).length == 0) {
                return "";
            }
            if (properties.split(COLON, VALID_NUM).length != VALID_NUM) {
                product.setStatus(-1);
                //销售属性组合字符串格式错误
                String message = I18nMessage.getMessage("yami.properties");
                String error = I18nMessage.getMessage("yami.properties.error");
                throw new YamiShopBindException(message + "[" + properties + "]" + error);
            }
            return stringBuilder.append(properties).toString();
        }
        Set<String> propertyKeySet = new HashSet<>();
        String[] property = properties.split(SEMICOLON);
        for (int i = 0; i < property.length; i++) {
            String prop = property[i];
            if (prop.split(COLON, 2).length != VALID_NUM) {
                product.setStatus(-1);
                //销售属性组合字符串格式错误
                String message = I18nMessage.getMessage("yami.properties");
                String error = I18nMessage.getMessage("yami.properties.error");
                throw new YamiShopBindException(message + "[" + properties + "]" + error);
            }
            String key = prop.split(COLON, 2)[0].trim();
            String value = prop.split(COLON, 2)[1].trim();
            if (propertyKeySet.contains(key)) {
                product.setStatus(-1);
                //规格名重复
                String message = I18nMessage.getMessage("yami.sku.name");
                String error = I18nMessage.getMessage("yami.is.duplicate");
                throw new YamiShopBindException(message + "[" + key + "]" + error);
            }
            if (StrUtil.isNotBlank(key)) {
                propertyKeySet.add(key);
            }
            if (StrUtil.isNotBlank(value) && i != property.length - 1) {
                stringBuilder.append(prop).append(";");
            } else if (i == property.length - 1) {
                stringBuilder.append(prop);
            }
        }
        if (propertyKeySet.isEmpty()) {
            return "";
        }
        return stringBuilder.toString();
    }

    private SkuAdminDTO getSku(Long shopId, Date date, double skuOriPrice, double skuPrice, Integer stocks, String partyCode, double weight, double volume,int stockWarning) {
        SkuAdminDTO sku = new SkuAdminDTO();
        sku.setOriPrice(skuOriPrice);
        sku.setPrice(skuPrice);
        sku.setStocks(Math.abs(stocks));
        sku.setSkuScore(0L);
        sku.setPartyCode(partyCode);
        sku.setVolume(Math.abs(volume));
        sku.setUpdateTime(date);
        sku.setWeight(Math.abs(weight));
        sku.setVersion(1);
        sku.setIsDelete(0);
        sku.setStatus(1);
        sku.setStockWarning(stockWarning);
        WarehouseVO warehouse = warehouseService.getDefaultWarehouseByShopId(shopId, SysTypeEnum.MULTISHOP.value());
        if (Objects.isNull(warehouse)) {
            throw new YamiShopBindException("yami.product.excel.import.notDefaultWarehouse");
        }
        Long warehouseId = warehouse.getWarehouseId();
        StockPointSkuDTO stockPointSkuDTO = new StockPointSkuDTO();
        stockPointSkuDTO.setStock(Math.abs(stocks));
        stockPointSkuDTO.setStockPointId(warehouseId);
        stockPointSkuDTO.setStockPointType(StockPointType.WAREHOUSE.getValue());
        stockPointSkuDTO.setType(0);
        sku.setStockPointList(Collections.singletonList(stockPointSkuDTO));
        return sku;
    }

    private void newProdInfo(Long shopId, boolean isSameCity, Map<String, Long> categoryMap, Map<String, Long> shopCategoryMap, Map<String, Long> transportMap, Boolean prodAudit, List<ProductParam> products, ProductParam product, Map<Integer, ProdLangParam> prodLangMap, String prodType, String prodCategory, String category, String shopCategory, String pic, String video, String dvyType, String transportName, String freightAmount, String statusStr, String writeOffNum, String writeOffMultipleCount, String writeOffTime, String writeOffStart, String writeOffend, String isRefund, String virtualRemark, Map<Integer, String> prodParameterLangMap, LangConfig langConfig) {
        ProdLangParam prodLang = prodLangMap.get(langConfig.getLang());
        // prodLang=null 是获取商品名称是为null所导致，归为商品名称为空报错
        if (Objects.isNull(prodLang) || StrUtil.isBlank(prodLang.getProdName())) {
            // 默认商品名称不能为空
            throw new YamiShopBindException("(" + I18nMessage.getMessage("yami.excel.prod.name") + "-" + langConfig.getName() + ") " + I18nMessage.getMessage("yami.not.null"));
        }
        if (StrUtil.isBlank(prodType)) {
            prodType = ProdType.PROD_TYPE_NORMAL.desc();
        }
        if (StrUtil.isBlank(prodCategory)) {
            // 商品类别不能为空
            throw new YamiShopBindException("yami.product.excel.import.categoryNull");
        }
        if(StrUtil.isBlank(shopCategory)||!shopCategoryMap.containsKey(shopCategory)){
            throw new YamiShopBindException("yami.product.excel.import.shopCategoryNull");
        }
        if (StrUtil.isBlank(category)) {
            // 平台分类不能为空
            throw new YamiShopBindException("yami.platform.category.not.blank");
        }
        if (StrUtil.isBlank(shopCategory)) {
            // 店铺分类不能为空
            throw new YamiShopBindException("yami.prod.shop.category.not.blank");
        }
        product.setProdName(prodLang.getProdName());
        product.setShopId(shopId);
        product.setProdLangList(new ArrayList<>(prodLangMap.values()));
        product.setCategoryId(categoryMap.get(category));
        if (Objects.isNull(product.getCategoryId())) {
            throw new YamiShopBindException(String.format(I18nMessage.getMessage("yami.product.excel.import.categoryDisable"), category));
        }
        product.setShopCategoryId(shopCategoryMap.get(shopCategory));
        if(StrUtil.isNotBlank(pic)){
            String[] split = pic.split(",");
            checkPic(split);
            product.setPic(pic.split(",")[0]);
            product.setImgs(pic);
        }

        if(StrUtil.isNotBlank(video)){
            checkVideo(video);
            product.setVideo(video);
        }

        product.setDeliveryModeVo(loadDvyType(dvyType, isSameCity,prodType));
        product.setDeliveryTemplateId(transportMap.get(transportName));
        if (Objects.equals(transportMap.get(transportName), DeliveryTemplateType.FREIGHT.getValue())) {
            double deliveryAmount = Double.parseDouble(freightAmount);
            if (deliveryAmount < Constant.MIN_PRODUCT_AMOUNT) {
                deliveryAmount = Constant.MIN_PRODUCT_AMOUNT;
            }
            product.setDeliveryAmount(deliveryAmount);
        }
        product.setStatus(Objects.equals(statusStr, "上架") ? ProdStatusEnums.NORMAL.getValue() : ProdStatusEnums.SHOP_OFFLINE.getValue());
        // 如果商品审核true，且是商品上架状态的则为待审核状态
        if (BooleanUtil.isTrue(prodAudit) && Objects.equals(product.getStatus(), ProdStatusEnums.NORMAL.getValue())) {
            product.setStatus(ProdStatusEnums.AUDIT.getValue());
        }
        if (StrUtil.equals(ProdType.PROD_TYPE_NORMAL.desc(), prodType)) {
            if (Objects.isNull(transportMap.get(transportName)) && !StrUtil.equals(ProdMoldEnum.VIRTUAL.desc(), prodCategory)) {
                // 运费模板不存在
                throw new YamiShopBindException("yami.prod.transport.not.exist");
            }
            if (Objects.equals(transportMap.get(transportName), DeliveryTemplateType.FREIGHT.getValue()) && StrUtil.equals(Constant.PICKUP_CODE_PREFIX, freightAmount)) {
                // 固定运费模板运费金额不能为空
                throw new YamiShopBindException("yami.trans.amount.not.null");
            }
            product.setDeliveryAmount(Double.parseDouble(freightAmount));
            product.setProdType(ProdType.PROD_TYPE_NORMAL.value());
        } else {
            product.setProdType(ProdType.PROD_TYPE_ACTIVE.value());
        }
        if (StrUtil.equals(ProdMoldEnum.REAL.desc(), prodCategory)) {
            product.setMold(0);
        } else {
            // 处理下虚拟商品信息
            handleVirtualProdInfo(product, prodType, writeOffNum, writeOffMultipleCount, writeOffTime, writeOffStart, writeOffend, isRefund, virtualRemark);
        }
        product.setProdParameterList(parseParameter(prodParameterLangMap));
        product.setContent("<p></p>");
        products.add(product);
    }

    private void checkVideo(String video) {
        try {
            URL url = new URL(video);
            String headerField = "content-type";
            String contentType = "video/mp4";
            String vedioEnd = ".mp4";
            Boolean isFormat = !(contentType.equals(url.openConnection().getHeaderField(headerField))||video.endsWith(vedioEnd));
            if(isFormat){
                throw new YamiShopBindException("yami.prod.video.format.error");
            }
        }  catch (IOException e) {
           log.error("视频格式判断出错:{}",e.getMessage());
        }
        int length = getFileLength(video);
        DecimalFormat df = new DecimalFormat("0.00");
        float lengthByMb = Float.parseFloat(df.format(length / (float) MB));
        if(lengthByMb > MAX_LENGTH_BYMGB){
            throw new YamiShopBindException("yami.prod.video.too.large.error");
        }
    }

    private int getFileLength(String url1) {
        int length=0;
        URL url;
        try {
            url = new  URL(url1);
            HttpURLConnection urlcon=(HttpURLConnection)url.openConnection();
            //根据响应获取文件大小
            length=urlcon.getContentLength();
            urlcon.disconnect();
        } catch (MalformedURLException e) {
            e.getMessage();
        } catch (IOException e) {
            e.getMessage();
        }
        return length;
    }

    private void checkPic(String[] split) {
        if(split.length > IMG_NUM){
            throw new YamiShopBindException("yami.prod.pic.limit");
        }
        for (String s : split) {
            if(!isImgUrl(s)){
                throw new YamiShopBindException("yami.prod.pic.format.error");
            }
        }
    }

    private boolean isImgUrl(String url) {
        if (url == null || url.trim().length() == 0) {
            return false;
        }
        String suffix = url.substring(url.lastIndexOf(".")+1);
        try {
            return IMG_URL_SUFFIX.matcher(suffix).matches()||IMG_URL.matcher(new URL(url).openConnection().getHeaderField("content-type")).matches();
        }  catch (IOException e) {
           log.error("图片判断出错:{}",e.getMessage());
        }
        return false;
    }

    private Product.DeliveryModeVO loadDvyType(String dvyType, Boolean isSameCity, String prodType) {
        Product.DeliveryModeVO mode = new Product.DeliveryModeVO();
        mode.setHasShopDelivery(true);
        mode.setHasUserPickUp(false);
        mode.setHasCityDelivery(false);
        if (Objects.isNull(dvyType)) {
            return mode;
        }
        if (dvyType.contains(DeliveryType.STATION.getDesc())) {
            mode.setHasUserPickUp(true);
        }


        if (dvyType.contains(DeliveryType.SAME_CITY.getDesc())) {
            mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
        }
        // 活动商品的配送方式都可以
        if(Objects.equals(prodType,ProdType.PROD_TYPE_ACTIVE.desc())){
            mode.setHasShopDelivery(true);
            mode.setHasUserPickUp(true);
            mode.setHasCityDelivery(true);
        }
        return mode;
    }

    private void handleVirtualProdInfo(ProductParam product, String prodType, String writeOffNum,String writeOffMultipleCount, String writeOffTime, String writeOffStart, String writeOffEnd, String isRefund, String virtualRemark) {
        if (StrUtil.equals(ProdType.PROD_TYPE_ACTIVE.desc(), prodType)) {
            throw new YamiShopBindException("yami.product.excel.import.virtualNotActivity");
        }
        if (StrUtil.isBlank(writeOffNum)) {
            throw new YamiShopBindException("yami.product.excel.import.virtualWriteOffNumNull");
        }
        ProdWriteOffNumEnum writeOffNumEnum = ProdWriteOffNumEnum.instance(writeOffNum);
        if (!Objects.equals(writeOffNumEnum, ProdWriteOffNumEnum.WRITE_OFF_NOT) && StrUtil.isBlank(writeOffTime)) {
            throw new YamiShopBindException("yami.product.excel.import.virtualWriteOffTimeNull");
        }
        // 多次核销次数
        int count;
        try {
            count = Math.max(Integer.parseInt(writeOffMultipleCount), 2);
        } catch (NumberFormatException e) {
            count = -1;
        }
        if (Objects.equals(writeOffNumEnum, ProdWriteOffNumEnum.WRITE_OFF_MULTIPLE)) {
            product.setWriteOffMultipleCount(count);
        }
        boolean validWriteOffTime = StrUtil.equals(writeOffTime, "自定义") && (StrUtil.isBlank(writeOffStart) || StrUtil.isBlank(writeOffEnd));
        if (validWriteOffTime) {
            throw new YamiShopBindException("yami.product.excel.import.virtualWriteOffTimeNull");
        }
        if (StrUtil.isBlank(isRefund)) {
            throw new YamiShopBindException("yami.product.excel.import.virtualCanRefundNull");
        }
        if (StrUtil.isBlank(writeOffTime)) {
            product.setWriteOffTime(null);
        } else {
            if(!TODAY.equals(writeOffTime)){
                writeOffTime = writeOffTime.replace("天", "");
            }
            int time = StrUtil.equals(writeOffTime, LONG_TERM) ? -1 : StrUtil.equals(writeOffTime, "当天有效") ? 1 : StrUtil.equals(writeOffTime, "自定义") ? 0 : Integer.parseInt(writeOffTime);
            product.setWriteOffTime(time);
        }
        product.setWriteOffNum(writeOffNumEnum.value().toString());

        if (StrUtil.isNotBlank(writeOffStart)) {
            product.setWriteOffStart(DateUtil.parse(writeOffStart, "yyyy-MM-dd HH:mm:ss"));
        }
        if (StrUtil.isNotBlank(writeOffEnd)) {
            product.setWriteOffEnd(DateUtil.parse(writeOffEnd, "yyyy-MM-dd HH:mm:ss"));
        }
        product.setIsRefund(StrUtil.equals("是", isRefund) ? 1 : 0);
        if(StrUtil.isNotBlank(virtualRemark)){
            if (!JSONValidator.Type.Array.equals(JSONValidator.from(virtualRemark).getType())) {
                throw new YamiShopBindException("yami.prod.import.virtualRemark.notValid");
            }
            List<Map> mapList = JSONObject.parseArray(virtualRemark, Map.class);
            if(mapList.size() > VIRTUAL_MSG_NUM){
                List newList = mapList.subList(0, 10);
                virtualRemark = JSON.toJSONString(newList);
            }
        }
        product.setVirtualRemark(StrUtil.isBlank(virtualRemark) ? null : virtualRemark);
        product.setMold(1);
    }

    public List<ProdParameter> parseParameter(String prodParameterStr, ProductParam product) {
        if (StrUtil.isBlank(prodParameterStr)) {
            return null;
        }
        if (!hasSemicolon(prodParameterStr)) {
            int length = prodParameterStr.split(COLON).length;
            if (length == 0) {
                return null;
            }
            if (length != VALID_NUM) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
        }
        // 商品参数名，值都可以相同，不做校验
        String[] prodParameters = prodParameterStr.split(SEMICOLON);
        List<ProdParameter> prodParameterList = new ArrayList<>();
        String key, value;
        for (String prodParameter : prodParameters) {
            if (prodParameter.split(COLON).length != VALID_NUM) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
            key = prodParameter.split(COLON)[0].trim();
            value = prodParameter.split(COLON)[1].trim();
            // 非空校验
            if (StrUtil.isBlank(key) || StrUtil.isBlank(value)) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
            if (key.length() > 10) {
                key = key.substring(0, 10);
            }
            if (value.length() > 20) {
                value = value.substring(0, 20);
            }
            ProdParameter dbParameter = new ProdParameter();
            dbParameter.setProdId(product.getProdId());
            dbParameter.setParameterKey(key);
            dbParameter.setParameterValue(value);
            prodParameterList.add(dbParameter);
        }
        return prodParameterList;
    }


    public List<ProdParameter> parseParameter(Map<Integer, String> prodParameterLangMap) {
        if (MapUtil.isEmpty(prodParameterLangMap)) {
            return null;
        }
        List<ProdParameter> prodParameterList = new ArrayList<>();
        for (Integer lang : prodParameterLangMap.keySet()) {
            String prodParameterStr = prodParameterLangMap.get(lang);
            if (StrUtil.isBlank(prodParameterStr)) {
                continue;
            }
            if (!hasSemicolon(prodParameterStr)) {
                int length = prodParameterStr.split(COLON).length;
                if (length == 0) {
                    return null;
                }
                if (length != VALID_NUM) {
                    throw new YamiShopBindException("yami.prod.parameter.format.error");
                }
            }

            // 商品参数名，值都可以相同，不做校验
            String[] prodParameters = prodParameterStr.split(SEMICOLON);
            String key, value;
            for (int i = 0; i < prodParameters.length; i++) {
                String prodParameter = prodParameters[i];
                if (prodParameter.split(COLON).length != VALID_NUM) {
                    throw new YamiShopBindException("yami.prod.parameter.format.error");
                }
                key = prodParameter.split(COLON)[0].trim();
                value = prodParameter.split(COLON)[1].trim();
                // 非空校验
                if (StrUtil.isBlank(key) || StrUtil.isBlank(value)) {
                    throw new YamiShopBindException("yami.prod.parameter.format.error");
                }
                if (key.length() > 10) {
                    key = key.substring(0, 10);
                }
                if (value.length() > 20) {
                    value = value.substring(0, 20);
                }
                ProdParameterLang prodParameterLang = new ProdParameterLang();
                prodParameterLang.setLang(lang);
                prodParameterLang.setParameterKey(key);
                prodParameterLang.setParameterValue(value);
                ProdParameter newProdParameter;
                if (prodParameterList.size() > i) {
                    newProdParameter = prodParameterList.get(i);
                } else {
                    newProdParameter = new ProdParameter();
                    newProdParameter.setProdParameterLangList(new ArrayList<>(Constant.INITIAL_CAPACITY));
                    prodParameterList.add(newProdParameter);
                }
                newProdParameter.getProdParameterLangList().add(prodParameterLang);
            }
        }
        return prodParameterList;
    }

    public boolean hasSemicolon(String str) {
        return Objects.nonNull(str) && str.contains(SEMICOLON);
    }


    private int writerProdData(ExcelWriter writer, Long shopId, ProductExportParam prod, ProductExportParam esProd, int firstRow, int lastRow, int col, Map<Long, String> categoryMap, Map<Long, String> shopCategoryMap, LangConfig langConfig, Integer lang) {
        // shopId: null-导出商城的商品,跟具体店铺无关
        if (Objects.isNull(shopId)) {
            // 店铺名称
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getShopName());
        }
        Map<Integer, ProdLang> prodLangMap = prod.getProdLangList().stream().collect(Collectors.toMap(ProdLang::getLang, p -> p));
        if (!prodLangMap.containsKey(langConfig.getLang())) {
            ProdLang newProdLang = new ProdLang();
            newProdLang.setLang(langConfig.getLang());
            ProdLang prodLang = prodLangMap.get(langManager.getLang(prodLangMap.keySet(), langConfig, lang));
            newProdLang.setProdName(prodLang.getProdName());
            newProdLang.setBrief(prodLang.getBrief());
            prodLangMap.put(langConfig.getLang(), newProdLang);
        }
        // 商品名称
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            ProdLang prodLang = prodLangMap.get(langItemConfig.getLang());
            if (Objects.nonNull(prodLang)) {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prodLang.getProdName());
            } else {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "");
            }
        }
        // 类型
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, ProdType.instance(esProd.getProdType()).desc());
        // 类别
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "虚拟商品" : "实物商品");
        // 平台分类
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, categoryMap.get(prod.getCategoryId()));
        // 店铺分类
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, shopCategoryMap.get(prod.getShopCategoryId()));
        // 商品图片
        StringBuilder sb = new StringBuilder();
        UploadFile config = shopConfig.getUploadFile();
        if(StringUtils.isNotBlank(prod.getImgs())){
            for (String pic : prod.getImgs().split(COMMA)) {
                sb.append(config.addResourceDomain(pic)).append(",");
            }
            sb.deleteCharAt(sb.length()-1);
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, sb.toString());
        // 商品视频
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, config.addResourceDomain(prod.getVideo()));
        // "卖点"
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            ProdLang prodLang = prodLangMap.get(langItemConfig.getLang());
            if (Objects.nonNull(prodLang)) {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prodLang.getBrief());
            } else {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "");
            }
        }
        // 商品详情
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            ProdLang prodLang = prodLangMap.get(langItemConfig.getLang());
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.isNull(prodLang) ? "" : prodLang.getContent());
        }
        Product.DeliveryModeVO deliveryModeVO = Json.parseObject(prod.getDeliveryMode(), Product.DeliveryModeVO.class);
        StringBuilder deliveryMode = new StringBuilder();
        if (deliveryModeVO != null && !Objects.equals(prod.getMold(), 1)) {
            if (deliveryModeVO.getHasCityDelivery() != null && deliveryModeVO.getHasCityDelivery()) {
                deliveryMode.append("同城配送，");
            }
            if (deliveryModeVO.getHasUserPickUp() != null && deliveryModeVO.getHasUserPickUp()) {
                deliveryMode.append("用户自提，");
            }
            if (deliveryModeVO.getHasShopDelivery() != null && deliveryModeVO.getHasShopDelivery()) {
                deliveryMode.append("快递配送，");
            }
            deliveryMode.deleteCharAt(deliveryMode.length() - 1);
        } else {
            deliveryMode.append("无需快递");
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, deliveryMode);
        return handleCol(writer, prod, esProd, firstRow, lastRow, col, langConfig);
    }

    private int handleCol(ExcelWriter writer, ProductExportParam prod, ProductExportParam esProd, int firstRow, int lastRow, int col, LangConfig langConfig) {
        // 运费设置
        String transName = prod.getTransName();
        if(Objects.nonNull(prod.getDeliveryTemplateId()) && prod.getDeliveryTemplateId() <= 0) {
            if (Objects.equals(DeliveryTemplateType.FREE_SHIPPING.getValue(), prod.getDeliveryTemplateId())) {
                transName = DeliveryTemplateType.FREE_SHIPPING.getName();
            } else if (Objects.equals(DeliveryTemplateType.FREIGHT.getValue(), prod.getDeliveryTemplateId())) {
                transName = DeliveryTemplateType.FREIGHT.getName();
            }
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "" : transName);
        // 统一运费
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "" : prod.getDeliveryAmount());
        // 状态
        if (Objects.equals(esProd.getStatus(), ProdStatusEnums.SHOP_OFFLINE.getValue())) {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "下架");
        } else if (Objects.equals(esProd.getStatus(), ProdStatusEnums.PLATFORM_OFFLINE.getValue())) {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "违规下架");
        } else {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, ProdStatusEnums.instance(esProd.getStatus()).getDesc());
        }
        // 核销数据处理
        String numStr = "";
        String writeOffMultipleCountStr = "";
        String timeStr = "";
        if (Objects.equals(prod.getMold(), 1)) {
            if (Objects.nonNull(prod.getWriteOffNum())) {
                numStr = prod.getWriteOffNum() == -1 ? "多次核销" : prod.getWriteOffNum() == 0 ? "无需核销" : "单次核销";
            }
            if (Objects.nonNull(prod.getWriteOffMultipleCount())) {
                writeOffMultipleCountStr = Objects.equals(prod.getWriteOffMultipleCount(), -1) ? "无限次" : prod.getWriteOffMultipleCount().toString();
            }
            if (Objects.nonNull(prod.getWriteOffTime())) {
                timeStr = prod.getWriteOffTime() == -1 ? "长期有效" : prod.getWriteOffTime() == 0 ? "自定义" : prod.getWriteOffTime() + "天";
            }
        }
        // 核销次数
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, numStr);
        // 多次核销次数
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, writeOffMultipleCountStr);
        // 核销有效期
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, timeStr);
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getWriteOffStart());
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getWriteOffEnd());
        String isRefundStr = "";
        //虚拟商品
        if(Objects.equals(prod.getMold(), 1)){
            isRefundStr = Objects.nonNull(prod.getIsRefund()) && prod.getIsRefund() == 0 ? "否" : "是";
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, isRefundStr);
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getVirtualRemark());
        //商品参数处理
        Map<Integer, List<ProdParameterLang>> prodParameterLangMap = getProdParameterLangMap(prod, langConfig);
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            List<ProdParameterLang> prodParameterLangList = prodParameterLangMap.get(langItemConfig.getLang());
            if (CollUtil.isEmpty(prodParameterLangList)) {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "");
            } else {
                PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, transParameter(prodParameterLangList));
            }
        }
        return col;
    }

    @NotNull
    private Map<Integer, List<ProdParameterLang>> getProdParameterLangMap(ProductExportParam prod, LangConfig langConfig) {
        Map<Integer, List<ProdParameterLang>> prodParameterLangMap = new HashMap<>(16);
        for (ProdParameter prodParameter : prod.getProdParameterList()) {
            if (CollUtil.isEmpty(prodParameter.getProdParameterLangList())) {
                continue;
            }
            boolean defaultLang = false;
            for (ProdParameterLang prodParameterLang : prodParameter.getProdParameterLangList()) {
                if (Objects.equals(prodParameterLang.getLang(), langConfig.getLang())) {
                    defaultLang = true;
                }
                List<ProdParameterLang> prodParameterLangList = prodParameterLangMap.get(prodParameterLang.getLang());
                if (CollUtil.isEmpty(prodParameterLangList)) {
                    prodParameterLangList = new ArrayList<>();
                    prodParameterLangMap.put(prodParameterLang.getLang(), prodParameterLangList);
                }
                prodParameterLangList.add(prodParameterLang);
            }
            if (!defaultLang) {
                List<ProdParameterLang> prodParameterLangList = prodParameterLangMap.get(langConfig.getLang());
                if (CollUtil.isEmpty(prodParameterLangList)) {
                    prodParameterLangList = new ArrayList<>();
                    prodParameterLangMap.put(langConfig.getLang(), prodParameterLangList);
                }
                prodParameterLangList.add(prodParameter.getProdParameterLangList().get(0));
            }
        }
        return prodParameterLangMap;
    }

    public String transParameter(List<ProdParameterLang> prodParameterList) {
        if (CollUtil.isEmpty(prodParameterList)) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < prodParameterList.size(); i++) {
            ProdParameterLang prodParameter = prodParameterList.get(i);
            builder.append(prodParameter.getParameterKey()).append(":").append(prodParameter.getParameterValue());
            if (i != prodParameterList.size() - 1) {
                builder.append(";");
            }
        }
        return builder.toString();
    }
    private List<String> getHeaderList(Long shopId, LangConfig langConfig, boolean template) {
        List<String> headerList = new ArrayList<>();
        // 店铺名称-平台导入店铺的商品需要用到该字段
        if (Objects.isNull(shopId)) {
            headerList.add(I18nMessage.getMessage("yami.excel.shop.name"));
        }
        // 商品名称
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            String prodName = I18nMessage.getMessage("yami.excel.prod.name") + "-" + langItemConfig.getName();
            if (Objects.equals(langItemConfig.getLang(), langConfig.getLang())) {
                prodName = prodName  + (template ? I18nMessage.getMessage("yami.excel.required.field") : "");
            }
            headerList.add(prodName);
        }
        // 商品类型
        headerList.add(I18nMessage.getMessage("yami.excel.prod.type"));
        // 商品类别
        headerList.add(I18nMessage.getMessage("yami.excel.prod.mold") + (template ? I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 平台分类
        headerList.add(I18nMessage.getMessage("yami.excel.platform.category") + (template ? I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 店铺分类
        headerList.add(I18nMessage.getMessage("yami.excel.shop.category") + (template ? I18nMessage.getMessage("yami.excel.required.field") : ""));
        // 商品图片
        headerList.add(I18nMessage.getMessage("yami.excel.prod.pic"));
        // 商品视频
        headerList.add(I18nMessage.getMessage("yami.excel.prod.video"));
        // 商品卖点
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.prod.point") + "-" + langItemConfig.getName());
        }
        // 商品详情
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.prod.info") + "-" + langItemConfig.getName());
        }
        // 配送方式
        headerList.add(I18nMessage.getMessage("yami.excel.delivery.method"));
        // 运费选择
        headerList.add(I18nMessage.getMessage("yami.excel.freight.selection") + (template ? I18nMessage.getMessage("yami.excel.freight.selection.required") : ""));
        // 固定运费
        headerList.add(I18nMessage.getMessage("yami.excel.shipping.fee") + (template ? I18nMessage.getMessage("yami.excel.freight.shipping.fee.required") : ""));
        // 商品状态
        headerList.add(I18nMessage.getMessage("yami.excel.prod.status"));
        // 核销次数
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.count") + (template ? I18nMessage.getMessage("yami.excel.virtual.goods.required") : ""));
        // 核销多次
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.many") + (template ? I18nMessage.getMessage("yami.excel.checkout.many.required") : ""));
        // 核销有效期(有效期，x天)
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.validity"));
        // 核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.start.time"));
        // 核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)
        headerList.add(I18nMessage.getMessage("yami.excel.checkout.end.time"));
        // 可以退款
        headerList.add(I18nMessage.getMessage("yami.excel.refundable") + (template ? I18nMessage.getMessage("yami.excel.virtual.goods.required") : ""));
        // 虚拟商品留言 格式是[{"name": "xx", "isRequired": true}]
        headerList.add(I18nMessage.getMessage("yami.excel.virtual.goods.message.format"));
        // 商品参数字符串 格式是k1:v1;k2:v2
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.commodity.parameter") + "-" + langItemConfig.getName());
        }
        // 商品sku名称
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.prod.sku.name") + "-" + langItemConfig.getName());
        }
        // 销售属性组合字符串 格式是p1:v1;p2:v2
        for (LangItemConfig langItemConfig : langConfig.getLangItemList()) {
            headerList.add(I18nMessage.getMessage("yami.excel.sales.attribute") + "-" + langItemConfig.getName());
        }
        // 原价
        headerList.add(I18nMessage.getMessage("yami.excel.original.price"));
        // 价格
        headerList.add(I18nMessage.getMessage("yami.excel.price"));
        // 库存
        headerList.add(I18nMessage.getMessage("yami.excel.stock"));
        // 商品编码
        headerList.add(I18nMessage.getMessage("yami.excel.prod.code"));
        // 商品重量
        headerList.add(I18nMessage.getMessage("yami.excel.prod.weight"));
        // 商品体积
        headerList.add(I18nMessage.getMessage("yami.excel.prod.volume"));
        // 库存预警
        headerList.add(I18nMessage.getMessage("yami.excel.sku.stock.warning"));
        return headerList;
    }

    private Sheet getSheet(ExcelWriter writer, Long shopId, Integer langSize) {
        Sheet sheet = writer.getSheet();
        int index = 0;
        if (Objects.isNull(shopId)) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 商品名称
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 60 * 256);
        }
        // 商品类型
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品类别
        sheet.setColumnWidth(index++, 20 * 256);
        // 平台分类
        sheet.setColumnWidth(index++, 40 * 256);
        // 店铺分类
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品图片
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品视频
        sheet.setColumnWidth(index++, 20 * 256);
        // 商品卖点
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 商品详情
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 60 * 256);
        }
        // 配送方式
        sheet.setColumnWidth(index++, 20 * 256);
        // 运费选择
        sheet.setColumnWidth(index++, 60 * 256);
        // 固定运费
        sheet.setColumnWidth(index++, 40 * 256);
        // 商品状态
        sheet.setColumnWidth(index++, 20 * 256);
        // 核销次数
        sheet.setColumnWidth(index++, 40 * 256);
        // 核销多次
        sheet.setColumnWidth(index++, 40 * 256);
        // 核销有效期(有效期，x天)
        sheet.setColumnWidth(index++, 40 * 256);
        // 核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)
        sheet.setColumnWidth(index++, 40 * 256);
        // 核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)
        sheet.setColumnWidth(index++, 40 * 256);
        // 可以退款
        sheet.setColumnWidth(index++, 20 * 256);
        // 虚拟商品留言 格式是[{"name": "xx", "isRequired": true}]
        sheet.setColumnWidth(index++, 40 * 256);
        // 商品参数字符串 格式是k1:v1;k2:v2
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 商品sku名称
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 销售属性组合字符串 格式是p1:v1;p2:v2
        for (int i = 0; i < langSize; i++) {
            sheet.setColumnWidth(index++, 40 * 256);
        }
        // 原价
        sheet.setColumnWidth(index++, 30 * 256);
        // 价格
        sheet.setColumnWidth(index++, 30 * 256);
        // 库存
        sheet.setColumnWidth(index++, 30 * 256);
        // 商品编码
        sheet.setColumnWidth(index++, 30 * 256);
        // 商品重量
        sheet.setColumnWidth(index++, 30 * 256);
        // 商品体积
        sheet.setColumnWidth(index++, 30 * 256);
        return sheet;
    }


    public static void setComment(Cell cell, String text, Sheet sheet){
        ClientAnchor anchor = new XSSFClientAnchor();
        anchor.setDx1(0);
        anchor.setDx2(0);
        anchor.setDy1(0);
        anchor.setDy2(0);
        anchor.setCol1(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setCol2(cell.getColumnIndex() + 5);
        anchor.setRow2(cell.getRowIndex() + 6);
        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(anchor);
        comment.setString(new XSSFRichTextString(text));
        cell.setCellComment(comment);
    }
}
